﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace DBUtility
{
    public class Util
    {

        public static List<T> GetFirstCol<T>(DataTable data)
        {
            List<T> lst = new List<T>();
            if (data == null || data.Rows.Count <= 0)
            {
                return lst;
            }
            else
            {
                foreach (DataRow dr in data.Rows)
                {
                    if (dr[0] != null)
                    {
                        T t = (T)Convert.ChangeType(dr[0], typeof(T));
                        lst.Add(t);
                    }
                }
            }
            return lst;
        }

        public static List<string> GetAllColumnNames(DataTable dt)
        {
            List<string> lst = new List<string>();
            if (dt != null && dt.Columns.Count > 0)
            {
                foreach (DataColumn dc in dt.Columns)
                {
                    lst.Add(dc.ColumnName);
                }
            }
            return lst;
        }


        public static T ConvertToObjectFromDB<T>(T obj, DataRow row)
        {
            Type type = obj.GetType();
            System.Reflection.PropertyInfo[] propInfo = type.GetProperties();
            for (int i = 0; i < propInfo.Length; i++)
            {
                if (row.Table.Columns[propInfo[i].Name] != null && row[propInfo[i].Name] != System.DBNull.Value)
                {
                    try
                    {
                        propInfo[i].SetValue(obj, row[propInfo[i].Name], null);
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.Message);
                    }
                }
            }
            return obj;
        }

        public static List<T> ConvertToListObject<T>(DataTable dt)
        {

            List<T> objs = new List<T>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                T obj = (T)Activator.CreateInstance(typeof(T));
                obj = ConvertToObjectFromDB(obj, dt.Rows[i]);
                objs.Add(obj);
            }
            return objs;
        }


        /// <summary>
        /// 根据数组成员拼接SQL in ，尤其是传入多个ID或Name。过滤SQL中应该包含 Where 1=1 至少一个过滤条件，因为方法返回 and xxx in。。。
        /// 使用时注意，members 成员中是否包含引号
        /// </summary>
        /// <param name="field">列名</param>
        /// <param name="members">成员数组</param>
        public static string SQLIn<T>(string field, params T[] members)
        {
            string result = string.Empty;
            if (members == null || members.Length <= 0)
            {
                return string.Empty;
            }
            List<string> lst = new List<string>();
            foreach (T obj in members)
            {

                if (obj != null)
                {
                    string val = obj.ToString();
                    if (val.Contains("'"))
                    {
                        continue;
                    }
                    lst.Add("'" + obj.ToString() + "'");
                }
            }
            result = " and " + field + " in (" + string.Join(",", lst) + ") ";
            return result;
        }

    }
}
